// 导入数据库模块
const db = require("../db/index.js");

// 发布启事
exports.public = (req, res) => {
  let thingInfo = req.body;
  const sql = `insert into thing_info(user_id,tag,title,content,img,location,TIME,status_id,type_id,
		audit_status,is_delete) values(?,?,?,?,?,?,?,?,?,?,?)`;
  // console.log(thingInfo);
  db.query(
    sql,
    [
      thingInfo.user_id,
      thingInfo.tag,
      thingInfo.title,
      thingInfo.content,
      thingInfo.img,
      thingInfo.location,
      thingInfo.TIME,
      thingInfo.status_id,
      thingInfo.type_id,
      thingInfo.audit_status,
      thingInfo.is_delete
    ],
    (err, result) => {
      if (err) {
        throw Error("Server Error");
      }
      if (result.affectedRows !== 1) {
        return res.send({
          code: 500,
          message: "发布失败",
        });
      }
      return res.send({
        code: 200,
        message: "发布成功",
      });
    }
  );
};

// 修改启事类别
exports.updateTypeById = (req, res) => {
  const sql = `update thing_type set TYPE = ? where id = ?`
  db.query(sql, [req.body.TYPE, req.body.id], (err, result) => {
    if (err) {
      throw Error('Server Error')
    }
    res.send({
      code: 200,
      message: '修改成功'
    })
  })
}

// 增加启事类别
exports.addThingType = (req, res) => {
  const sql = `insert into thing_type(TYPE)values(?)`
  db.query(sql, req.body.TYPE, (err, result) => {
    if (err) {
      throw Error('Server Error')
    }
    res.send({
      code: 200,
      message: '增加成功'
    })
  })
}

// 删除启事类别
exports.removeThingType = (req, res) => {
  const sql = `delete from thing_type where id = ?`
  db.query(sql, req.query.id, (err, result) => {
    if (err) {
      throw Error('Server Error')
    }
    res.send({
      code: 200,
      message: '删除成功'
    })
  })
}


// 获取全部类别
exports.getAllThingType = (req, res) => {
  const pageNo = req.query.pageNo
  const pageSize = req.query.pageSize
  const startIndex = (pageNo - 1) * pageSize
  const sql = `select * from thing_type`
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    const data = result.slice(startIndex, startIndex + pageSize)
    // console.log(data);
    res.send({
      code: 200,
      data: {
        dataLength: result.length,
        result,
        data
      }
    })
  })
}

// 获取所有启事类型并统计
exports.getThingType = (req, res) => {
  let sql = `SELECT tt.id,tt.TYPE,COUNT(ti.type_id) AS type_count 
				FROM thing_type tt 
				LEFT JOIN thing_info ti 
				ON ti.type_id = tt.id AND ti.is_delete = 0 AND ti.audit_status = '已审核'
				GROUP BY tt.TYPE`;
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    res.send({
      code: 200,
      data: result,
    });
  });
};

// 获取所有启事状态并统计
exports.getThingStatus = (req, res) => {
  let sql = `SELECT ts.id,ts.STATUS,COUNT(ti.status_id) AS status_count 
				FROM thing_status ts
				LEFT JOIN thing_info ti 
				ON ti.status_id = ts.id AND ti.is_delete = 0 AND ti.audit_status = '已审核'
				GROUP BY ts.STATUS`;
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    res.send({
      code: 200,
      data: result,
    });
  });
};

// 修改启事信息
exports.updateInfoById = (req, res) => {
  const thingInfo = req.body
  console.log(thingInfo);
  const sql = `update thing_info set user_id = ?,tag = ?,title = ?,content = ?, 
  location = ? ,TIME = ?,look_count = ?,is_delete = ?,audit_status = ?,status_id = ?,img = ? where id = ?`
  db.query(sql, [thingInfo.user_id, thingInfo.tag, thingInfo.title, thingInfo.content,
  thingInfo.location, thingInfo.TIME, thingInfo.look_count, thingInfo.is_delete,
  thingInfo.audit_status, thingInfo.status_id,thingInfo.img, thingInfo.id], (err, result) => {
    if (err) {
      throw Error('Server Error')
    }
    res.send({
      code: 200,
      message: '修改成功'
    })
  })
}

// 删除启事信息
exports.removeThingInfo = (req, res) => {
  const sql = `update thing_info set is_delete = 1 where id = ?`
  db.query(sql, req.body.id, (err, result) => {
    if (err) {
      throw Error('Server Error')
    }
    res.send({
      code: 200,
      message: '删除成功'
    })
  })
}


// 获取全部启事信息（分页）
exports.getAllThingInfo = (req, res) => {
  const pageNo = req.query.pageNo
  const pageSize = Number(req.query.pageSize)
  const key = req.query.key
  const status = req.query.status
  const tag = req.query.tag
  const time = req.query.TIME
  const startIndex = (pageNo - 1) * pageSize
  const sql = `select * from thing_info`
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    let data = null
    let resultData = JSON.parse(JSON.stringify(result))
    if (typeof time !== 'undefined') {
      resultData = resultData.filter(item => {
        return new Date(item.TIME).getTime() >= time[0]
          && new Date(item.TIME).getTime() <= time[1]
      })
    }

    resultData.forEach(item => {
      Object.keys(item).forEach(key => {
        if (item[key] === null) {
          item[key] = 0
        }
        if (key === 'is_delete') {
          item[key] = item[key] === 0 ? '否' : '是'
        }
      })
    })
    if (!key && !status && !tag) {
      data = resultData.slice(startIndex, startIndex + pageSize)
    } else {
      data = resultData.filter(item => (item.title.includes(key) || item.content.includes(key))
        && item.tag.includes(tag) && item.audit_status.includes(status))
        .slice(startIndex, startIndex + pageSize)
    }

    res.send({
      code: 200,
      data: {
        dataLength: result.length,
        data,
        result
      }
    })
  })
}

// 根据标签获取全部启事
exports.getAllThingByTag = (req, res) => {
  const pageSize =  Number(req.query.pageSize)
  const pageName = req.query.pageName
  const statusId = req.query.statusId
  console.log(statusId);
  let sql = `SELECT  t.id, u.head, u.money_code,u.sex,u.signature,t.user_id, t.tag, t.type_id,tt.TYPE, t.title, t.content, t.img, t.location, t.TIME, t.status_id, t.look_count,
  t.is_delete,t.audit_status,COUNT(c.ownerId) AS thing_comment_count
  FROM thing_info t 
  LEFT JOIN thing_type tt
  ON t.type_id = tt.id
  LEFT JOIN COMMENT c 
  ON  c.ownerId = t.id 
  LEFT JOIN users u
  ON u.user_id = t.user_id 
  WHERE t.is_delete = 0
  GROUP BY t.id
  `;
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    let data = null
    let dataLength = 0
    if (pageName === 'index') {
      data = result.filter(item => item.audit_status === '已审核' && item.status_id === Number(statusId)).slice(0, pageSize)
      dataLength = result.filter(item => item.audit_status === '已审核' && item.status_id === Number(statusId)).length
    } else {
      data = result.slice(0, pageSize)
      dataLength = result.length
    }

    res.send({
      code: 200,
      data: {
        data,
        dataLength,
        result,
      }
    });
  });
};

// 根据启事id获取启事信息
exports.getThingInfoById = (req, res) => {
 const id = req.query.id
  let sql = `SELECT  t.id, u.head, u.money_code,u.sex,u.signature,t.user_id, t.tag, t.type_id,tt.TYPE, t.title, t.content, t.img, t.location, t.TIME, t.status_id, t.look_count,
  t.is_delete,t.audit_status,COUNT(c.ownerId) AS thing_comment_count
  FROM thing_info t 
  LEFT JOIN thing_type tt
  ON t.type_id = tt.id
  LEFT JOIN COMMENT c 
  ON  c.ownerId = t.id 
  LEFT JOIN users u
  ON u.user_id = t.user_id 
  WHERE t.is_delete = 0
  GROUP BY t.id
  `;
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    let data = result.filter(item => item.id ===  Number(id))
   
    res.send({
      code: 200,
      data
    });
  });
};


// 根据类型获取全部启事
exports.getAllThingByType = (req, res) => {
  const pageSize =  Number(req.query.pageSize)
  const pageName = req.query.pageName
  const typeId = req.query.typeId
  let sql = `SELECT  t.id, u.head, u.money_code,u.sex,u.signature,t.user_id, t.tag, t.type_id,tt.TYPE, t.title, t.content, t.img, t.location, t.TIME, t.status_id, t.look_count,
  t.is_delete,t.audit_status,COUNT(c.ownerId) AS thing_comment_count
  FROM thing_info t 
  LEFT JOIN thing_type tt
  ON t.type_id = tt.id
  LEFT JOIN COMMENT c 
  ON  c.ownerId = t.id 
  LEFT JOIN users u
  ON u.user_id = t.user_id 
  WHERE t.is_delete = 0
  GROUP BY t.id
  `;
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    let data = null
    let dataLength = 0
    if (pageName === 'index') {
      data = result.filter(item => item.audit_status === '已审核' && item.type_id === Number(typeId)).slice(0, pageSize)
      dataLength = result.filter(item => item.audit_status === '已审核' && item.type_id === Number(typeId)).length
    } else {
      data = result.slice(0, pageSize)
      dataLength = result.length
    }

    res.send({
      code: 200,
      data: {
        data,
        dataLength,
        result,
      }
    });
  });
};

// 根据关键字搜索
exports.getThingByKey = (req,res) =>{
  const key = req.query.key
  const pageSize =  Number(req.query.pageSize)
  const sql =  `SELECT  t.id, u.head, u.money_code,u.sex,u.signature,t.user_id, t.tag, t.type_id,tt.TYPE, t.title, t.content, t.img, t.location, t.TIME, t.status_id, t.look_count,
  t.is_delete,t.audit_status,COUNT(c.ownerId) AS thing_comment_count
  FROM thing_info t 
  LEFT JOIN thing_type tt
  ON t.type_id = tt.id
  LEFT JOIN COMMENT c 
  ON  c.ownerId = t.id 
  LEFT JOIN users u
  ON u.user_id = t.user_id 
  WHERE t.is_delete = 0
  GROUP BY t.id
  `;
  db.query(sql,(err,result) =>{
    if(err){
      throw Error("Server Error");
    }
    let data = result.filter(item => item.audit_status === '已审核' && (item.title.includes(key)) || item.content.includes(key)).slice(0, pageSize)
    let dataLength = result.filter(item => item.audit_status === '已审核' && (item.title.includes(key)) || item.content.includes(key)).length
    console.log(dataLength);
    res.send({
      code: 200,
      data: {
        data,
        dataLength,
        result,
      }
    });
  })
}

// 获取全部启事
exports.getAllThing = (req, res) => {
  const pageSize =  Number(req.query.pageSize)
  const pageName = req.query.pageName
  const user_id = req.query.user_id
  let sql = `SELECT  t.id, u.head, u.money_code,u.sex,u.signature,t.user_id, t.tag, t.type_id,tt.TYPE, t.title, t.content, t.img, t.location, t.TIME, t.status_id, t.look_count,
  t.is_delete,t.audit_status,COUNT(c.ownerId) AS thing_comment_count
  FROM thing_info t 
  LEFT JOIN thing_type tt
  ON t.type_id = tt.id
  LEFT JOIN COMMENT c 
  ON  c.ownerId = t.id 
  LEFT JOIN users u
  ON u.user_id = t.user_id 
  WHERE t.is_delete = 0
  GROUP BY t.id
  `;
  db.query(sql, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    let data = null
    let dataLength = 0
    if (pageName === 'index') {
      data = result.filter(item => item.audit_status === '已审核').slice(0, pageSize)
      dataLength = result.filter(item =>  item.audit_status === '已审核').length
    } else {
      data = result.filter(item => item.user_id === user_id).slice(0, pageSize)
      dataLength = result.filter(item => item.user_id === user_id).length
    }
    console.log(data);
    res.send({
      code: 200,
      data: {
        data,
        dataLength,
        result,
      }
    });
  });
};

exports.removeThing = (req, res) => {
  const sql = `update thing_info set is_delete = 1 WHERE id = ?`;
  console.log(req.body.id);
  db.query(sql, req.body.id, (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    res.send({
      code: 200,
      message: "删除成功",
    });
  });
};

exports.updateStatus = (req, res) => {
  const sql = `update thing_info set status_id = ? where id = ?`;
  db.query(sql, [req.body.statusId, req.body.id], (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    res.send({
      code: 200,
      message: "修改成功",
    });
  });
};

// 埋点数据PV上报
exports.logPv = (req, res) => {
  const sql = `update thing_info set look_count = ? where id = ?`;
  db.query(sql, [req.body.look_count, req.body.id], (err, result) => {
    if (err) {
      throw Error("Server Error");
    }
    res.send({
      code: 200,
    });
  });
};
